﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MSBIHelper.Helper;

namespace MSBIHelper.UI
{
    public partial class Frm_DW : Form
    {
       
        public Frm_DW()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Server=g9w2358.americas.hpqcorp.net,2048;Database=RZ_CLQR;User Id=clqrdevx;Password =c0+cl0c09a+4 ;
            try
            {
                DbHelper _dbhelper = new DbHelper(tb_Server.Text.Trim(),tbx_user.Text.Trim(),tbx_pwd.Text.Trim());
                DataTable dt = _dbhelper.ExecuteDataTable("select name from master.dbo.sysdatabases");

                combx_dbs.Items.Clear();
                foreach (DataRow dr in dt.Rows)
                {
                    combx_dbs.Items.Add(dr[0].ToString());
                }
                if (dt.Rows.Count > 0)
                    combx_dbs.Text = dt.Rows[0][0].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
           
        }

        private void combx_dbs_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbxlst_tables.Visible = true;
            if (panel2.Controls.Find("rtbMsg",true) != null)
            { 
                panel2.Controls.RemoveByKey("rtbMsg");
            }
            DbHelper _dbhelper = new DbHelper(tb_Server.Text.Trim(), tbx_user.Text.Trim(), tbx_pwd.Text.Trim());
            string sqlquery = string.Format(
            @"
            SELECT S.NAME+'.'+ T.NAME AS TABLENAME
             FROM  {0}.SYS.TABLES  AS T
             JOIN  {0}.SYS.SCHEMAS AS S 
             ON T.SCHEMA_ID  = S.SCHEMA_ID
            ORDER BY CASE WHEN RIGHT(T.NAME,2)='_D' THEN 1
						   WHEN RIGHT(T.NAME,3)='_F' THEN 2
						   ELSE 3
						   END ",
             combx_dbs.Text
             );
            try
            {
                DataTable dt = _dbhelper.ExecuteDataTable(sqlquery);
                this.cbxlst_tables.DataSource = dt;
                this.cbxlst_tables.DisplayMember = "TABLENAME";
                this.cbxlst_tables.ValueMember = "TABLENAME"; 
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                string tsql = Util.GetStringFromResource("Tsql_AddDummyRow");
                DbHelper _dbhelper = new DbHelper(tb_Server.Text.Trim(), tbx_user.Text.Trim(), tbx_pwd.Text.Trim());
                for (int i = 0; i < cbxlst_tables.Items.Count; i++)
                {
                    if (cbxlst_tables.GetItemCheckState(i).ToString().Equals("Checked"))
                    { 
                        _dbhelper.ExecuteNonQuery(
                            tsql.Replace("||TableName||", cbxlst_tables.GetItemText(cbxlst_tables.Items[i]))
                            .Replace("||DBNAME||", combx_dbs.Text)
                            );
                    }
                }
                MessageBox.Show("done!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            } 
        }
    }
}
